package jp.co.dailyReport.expense.sql;

import java.util.Calendar;
import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.apache.log4j.Logger;
import jp.co.dailyReport.Executer;
import jp.co.dailyReport.expense.ExpenseManufacturingCondition;

/**
 * 仕掛計上結果件数を取得するクラスです。
 */
public class ExpenseManufacturingCountExecuter implements Executer {

	private static Logger log = Logger.getLogger(ExpenseManufacturingCountExecuter.class);
	private StringBuffer sqlStatement;
	private List searchObjectList;
	private Integer customerId;
	private Integer projectId;
	private Integer staffId;
	private java.sql.Date startWorkDate;
	private java.sql.Date endWorkDate;

	public ExpenseManufacturingCountExecuter(ExpenseManufacturingCondition expenseManufacturingCondition) {
		searchObjectList = new ArrayList();
		sqlStatement = new StringBuffer();
		if(expenseManufacturingCondition.getCustomerId()!=0) 
			setCustomerId(expenseManufacturingCondition.getCustomerId());
		if(expenseManufacturingCondition.getProjectId()!=0) 
			setProjectId(expenseManufacturingCondition.getProjectId());
		if(expenseManufacturingCondition.getStaffId()!=0) 
			setStaffId(expenseManufacturingCondition.getStaffId());
		if(expenseManufacturingCondition.getStartWorkDate()!=null) 
			setStartWorkDate(expenseManufacturingCondition.getStartWorkDate());
		if(expenseManufacturingCondition.getEndWorkDate()!=null) 
			setSearchWorkDateEnd(expenseManufacturingCondition.getEndWorkDate());
	}

	private void appendAND(String str){
		if(sqlStatement.length() > 0)
			sqlStatement.append(" AND " + str);
		else
			sqlStatement.append(str);
	}

	private void appendOR(String str){
		if(sqlStatement.length() > 0)
			sqlStatement.append(" OR " + str);
		else
			sqlStatement.append(str);
	}

	public void setCustomerId(int customerId) {
		this.customerId = new Integer(customerId);
		searchObjectList.add(this.customerId);
		appendAND("c.customerId = ?");
	}

	public void setProjectId(int projectId) {
		this.projectId = new Integer(projectId);
		searchObjectList.add(this.projectId);
		appendAND("p.projectId = ?");
	}

	public void setStaffId(int staffId) {
		this.staffId = new Integer(staffId);
		searchObjectList.add(this.staffId);
		appendAND("s.staffId = ?");
	}

	public void setStartWorkDate(Calendar startWorkDate){
	  this.startWorkDate = new java.sql.Date(startWorkDate.getTime().getTime());
	  this.searchObjectList.add(this.startWorkDate);
	  appendAND("d.workDate >= ?");
	}

	public void setSearchWorkDateEnd(Calendar endWorkDate){
	  this.endWorkDate = new java.sql.Date(endWorkDate.getTime().getTime());
	  this.searchObjectList.add(this.endWorkDate);
	  appendAND("d.workDate <= ?");
	}

	/**
	 * 設定された条件を基に、仕掛計上結果件数を取得します。
	 * 
	 * @param conn コネクションオブジェクト
	 * @return 仕掛計上結果件数
	 * @throws SQLException
	 */
	public Object[] execute(Connection conn) throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rset = null;
		Integer answer = null;

		String sql = "select count(DISTINCT d.workDate,s.staffId,p.projectId) AS count " +
					 "from dailyReport AS d " +
					 "LEFT JOIN staff AS s ON d.staffId = s.staffId " +
					 "LEFT JOIN dailyReportWorkItem AS dw ON dw.dailyReportId = d.dailyReportId " +
					 "LEFT JOIN workItem AS w ON dw.workItemId = w.workItemId " +
					 "LEFT JOIN project AS p ON w.projectId = p.projectId " +
					 "LEFT JOIN customer AS c ON p.customerId = c.customerId";

		if(sqlStatement.length() > 0){
			sql = sql + " where " + sqlStatement.toString() +
			" order by s.staffId,d.workDate,c.customerId,p.projectId";
		} else {
			sql = sql +
			" order by s.staffId,d.workDate,c.customerId,p.projectId";
		}
		log.debug("sql: " + sql);

		try {
			pstmt = conn.prepareStatement(sql);
			if(this.customerId != null){
				pstmt.setInt(this.searchObjectList.indexOf(this.customerId)+1, this.customerId.intValue());
				releaseSearchObject(this.customerId);
			}
			if(this.projectId != null){
				pstmt.setInt(this.searchObjectList.indexOf(this.projectId)+1, this.projectId.intValue());
				releaseSearchObject(this.projectId);
			}
			if(this.staffId != null){
				pstmt.setInt(this.searchObjectList.indexOf(this.staffId)+1, this.staffId.intValue());
				releaseSearchObject(this.staffId);
			}
			if(this.startWorkDate != null){
				pstmt.setDate(this.searchObjectList.indexOf(this.startWorkDate)+1, this.startWorkDate);
				releaseSearchObject(this.startWorkDate);
			}
			if(this.endWorkDate != null){
				pstmt.setDate(this.searchObjectList.indexOf(this.endWorkDate)+1, this.endWorkDate);
				releaseSearchObject(this.endWorkDate);
			}
			rset = pstmt.executeQuery();
			if (rset.next()) {
				answer = new Integer(rset.getInt("count"));
			} else {
				throw new SQLException("no data found");
			}
		} finally {
			if(rset != null)
				rset.close();
			if(pstmt != null)
				pstmt.close();
		}
		return new Integer[] { answer };
	}

	private void releaseSearchObject(Object obj){
		searchObjectList.set(searchObjectList.indexOf(obj), null);
	}
}

